package it.gestoreContratti.database;

import it.gestoreContratti.database.exception.DatabaseConnectionException;
import it.gestoreContratti.database.exception.DatabaseNotFoundException;
import it.gestoreContratti.database.exception.DriverNotFoundException;
import it.gestoreContratti.database.exception.QueryException;
import it.gestoreContratti.entity.Contratto;
import it.gestoreContratti.entity.Periodo;
import it.gestoreContratti.entity.Specifica;

import java.io.File;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Vector;

public class DatabaseController {
	
	private File dbFile;
	private Connection connection;
	private DateFormat insertDateFormat;
	
	public DatabaseController(String path) {
		dbFile = new File(path);
		insertDateFormat = new SimpleDateFormat("yyyy-MM-dd");
	}
	
	public void verifyDbExist() throws DatabaseNotFoundException{
		if(!dbFile.exists()){
			throw new DatabaseNotFoundException();
		}
	}
	
	public void loadDriverClass()throws DriverNotFoundException{
		try {
			Class.forName("org.sqlite.JDBC");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			throw new DriverNotFoundException();
		}
	}
	
	public void connect() throws DatabaseConnectionException{
		try {
			connection = DriverManager.getConnection("jdbc:sqlite:"+dbFile.getAbsolutePath());
		} catch (SQLException e) {
			throw new DatabaseConnectionException();
		}
	}
	
	public void insertContratto(Contratto c) throws DatabaseConnectionException, QueryException{
		try {
			connection = DriverManager.getConnection("jdbc:sqlite:"+dbFile.getAbsolutePath());
		} catch (SQLException e) {			
			throw new DatabaseConnectionException();
			
		}
		String query = new String("insert into contratto " +
				"(numero, ditta, data_inizio, durata, periodo, data_scadenza, importo, rimanenza, commento) " +
				"values (?,?,'"+insertDateFormat.format(c.getDataInizio())+"',?,?,'"+insertDateFormat.format(c.getDataScadenza())+"',?,?,?)");
		try{
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setInt(1, c.getNumero().intValue());
			ps.setString(2, c.getDitta());			
			ps.setInt(3, c.getDurata().intValue());
			ps.setString(4, c.getPeriodo().toString());			
			ps.setDouble(5, c.getImporto().doubleValue());
			ps.setDouble(6, c.getRimanenza().doubleValue());
			ps.setString(7, c.getCommento());
			ps.execute();
			connection.close();
		} catch (SQLException e) {
			throw new QueryException("Impossibile inserire il nuovo contratto", e.getErrorCode());
		}
	}
	
	public Vector<Contratto> selectContrattiScadenza() throws DatabaseConnectionException, QueryException{
		try {
			connection = DriverManager.getConnection("jdbc:sqlite:"+dbFile.getAbsolutePath());
		} catch (SQLException e) {			
			throw new DatabaseConnectionException();			
		}
		Calendar oggi = new GregorianCalendar();
		Calendar soglia = new GregorianCalendar();
		soglia.add(Calendar.MONTH, 1);
		String query = "select numero, ditta, data_inizio, durata, periodo,data_scadenza," +
				" importo, rimanenza, commento  from contratto " +
				"where data_scadenza < '"+insertDateFormat.format(soglia.getTime())+"' " +
						"and data_scadenza >= '"+insertDateFormat.format(oggi.getTime())+"'";
		
		
		try {
			PreparedStatement ps = connection.prepareStatement(query);			
			ResultSet rs = ps.executeQuery();
			Vector<Contratto> contrattiScadenza = new Vector<Contratto>();
			while (rs.next()) {
				Contratto c = new Contratto();
				c.setNumero(new BigDecimal(rs.getInt(1)));
				c.setDitta(rs.getString(2));
				c.setDataInizio(insertDateFormat.parse(rs.getString(3)));
				c.setDurata(new BigDecimal(rs.getInt(4)));				
				if(rs.getString(5).equals("GG")){
					c.setPeriodo(Periodo.GG);
				} else{
					c.setPeriodo(Periodo.MM);
				}
				c.setDataScadenza(insertDateFormat.parse(rs.getString(6)));
				c.setImporto(new BigDecimal(rs.getDouble(7)));
				c.setRimanenza(new BigDecimal(rs.getDouble(8)));
				c.setCommento(rs.getString(9));
				contrattiScadenza.add(c);
			}
			connection.close();
			return contrattiScadenza;
			
		} catch (SQLException e) {
			throw new QueryException("Impossibile selezionare i contratti in scadenza", e.getErrorCode());
		} catch (ParseException e) {			
			throw new QueryException("Impossibile leggere le date", -1);
		}
		
	}
	
	public Vector<Contratto> selectContratti() throws DatabaseConnectionException, QueryException{
		try {
			connection = DriverManager.getConnection("jdbc:sqlite:"+dbFile.getAbsolutePath());
		} catch (SQLException e) {			
			throw new DatabaseConnectionException();			
		}
		String query = "select numero, ditta, data_inizio, durata, periodo,data_scadenza," +
				" importo, rimanenza, commento  from contratto ";		
		
		try {
			PreparedStatement ps = connection.prepareStatement(query);			
			ResultSet rs = ps.executeQuery();
			Vector<Contratto> contratti = new Vector<Contratto>();
			while (rs.next()) {
				Contratto c = new Contratto();
				c.setNumero(new BigDecimal(rs.getInt(1)));
				c.setDitta(rs.getString(2));
				c.setDataInizio(insertDateFormat.parse(rs.getString(3)));
				c.setDurata(new BigDecimal(rs.getInt(4)));				
				if(rs.getString(5).equals("GG")){
					c.setPeriodo(Periodo.GG);
				} else{
					c.setPeriodo(Periodo.MM);
				}
				c.setDataScadenza(insertDateFormat.parse(rs.getString(6)));
				c.setImporto(new BigDecimal(rs.getDouble(7)));
				c.setRimanenza(new BigDecimal(rs.getDouble(8)));
				c.setCommento(rs.getString(9));
				contratti.add(c);
			}
			connection.close();
			return contratti;
			
		} catch (SQLException e) {
			throw new QueryException("Impossibile selezionare i contratti", e.getErrorCode());
		} catch (ParseException e) {
			throw new QueryException("Impossibile leggere le date", -1);
		}
		
	}
	
	public void modificaContratto(Contratto vecchio, Contratto nuovo ) throws DatabaseConnectionException, QueryException{
		try {
			connection = DriverManager.getConnection("jdbc:sqlite:"+dbFile.getAbsolutePath());
		} catch (SQLException e) {			
			throw new DatabaseConnectionException();
			
		}
		String query = new String("update contratto set " +
				"numero = ?, ditta = ?, data_inizio = '"+insertDateFormat.format(nuovo.getDataInizio())+"', durata = ?, periodo = ?, " +
				"data_scadenza  = '"+insertDateFormat.format(nuovo.getDataScadenza())+"', importo = ?, rimanenza = ?, commento = ? where " +
				"numero = ?");
		try{
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setInt(1, nuovo.getNumero().intValue());
			ps.setString(2, nuovo.getDitta());			
			ps.setInt(3, nuovo.getDurata().intValue());
			ps.setString(4, nuovo.getPeriodo().toString());			
			ps.setDouble(5, nuovo.getImporto().doubleValue());
			ps.setDouble(6, nuovo.getRimanenza().doubleValue());
			ps.setString(7, nuovo.getCommento());
			ps.setInt(8, vecchio.getNumero().intValue());
			ps.execute();
			connection.close();
		} catch (SQLException e) {
			throw new QueryException("Impossibile modificare il contratto", e.getErrorCode());
		}
	}
	
	
	public boolean contrattoEsistente(BigDecimal numero) throws DatabaseConnectionException, QueryException{
		try {
			connection = DriverManager.getConnection("jdbc:sqlite:"+dbFile.getAbsolutePath());
		} catch (SQLException e) {			
			throw new DatabaseConnectionException();			
		}
		String query = "select numero from contratto where numero = ? ";		
		try {
			PreparedStatement ps = connection.prepareStatement(query);	
			ps.setInt(1, numero.intValue());
			ResultSet rs = ps.executeQuery();
			boolean esiste = rs.next(); 
			connection.close();
			return esiste;
			
		} catch (SQLException e) {
			throw new QueryException("Impossibile verificare se il contratto esiste", e.getErrorCode());
		}
		
	}
	
	public void prorogaContratto(BigDecimal numero, BigDecimal durata, java.util.Date dataScadenza ) throws DatabaseConnectionException, QueryException{
		try {
			connection = DriverManager.getConnection("jdbc:sqlite:"+dbFile.getAbsolutePath());
		} catch (SQLException e) {			
			throw new DatabaseConnectionException();
			
		}
		String query = new String("update contratto set " +
				"durata = ?, data_scadenza  = '"+insertDateFormat.format(dataScadenza)+"' " +
				"where numero = ?");
		try{
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setInt(1, durata.intValue());			
			ps.setInt(2, numero.intValue());			
			ps.execute();
			connection.close();
		} catch (SQLException e) {
			throw new QueryException("Impossibile prorogare il contratto", e.getErrorCode());
		}
	}
	
	public void eliminaContratto(Contratto contratto ) throws DatabaseConnectionException, QueryException{
		try {
			connection = DriverManager.getConnection("jdbc:sqlite:"+dbFile.getAbsolutePath());
		} catch (SQLException e) {			
			throw new DatabaseConnectionException();
			
		}
		String query = new String("delete from contratto where numero = ?");
		try{
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setInt(1, contratto.getNumero().intValue());			
			ps.execute();
			connection.close();
		} catch (SQLException e) {
			throw new QueryException("Impossibile eliminare il contratto", e.getErrorCode());
		}
	}	
	
	
	public boolean specificaEsistente(BigDecimal numero) throws DatabaseConnectionException, QueryException{
		try {
			connection = DriverManager.getConnection("jdbc:sqlite:"+dbFile.getAbsolutePath());
		} catch (SQLException e) {			
			throw new DatabaseConnectionException();			
		}
		String query = "select numero from specifica where numero = ? ";		
		try {
			PreparedStatement ps = connection.prepareStatement(query);	
			ps.setInt(1, numero.intValue());
			ResultSet rs = ps.executeQuery();				
			boolean esiste = rs.next(); 
			connection.close();
			return esiste;
			
		} catch (SQLException e) {
			throw new QueryException("Impossibile verificare se la specifica esiste", e.getErrorCode());
		}		
	}
	
	public void inserisciSpecifica(Specifica s)throws DatabaseConnectionException, QueryException{
		try {
			connection = DriverManager.getConnection("jdbc:sqlite:"+dbFile.getAbsolutePath());
		} catch (SQLException e) {			
			throw new DatabaseConnectionException();			
		}
		String query = new String("insert into specifica " +
				"(numero, data_inizio, termine_consegna, data_scadenza, importo, numero_contratto) " +
				"values (?,'"+insertDateFormat.format(s.getDataInizio())+"',?,'"+insertDateFormat.format(s.getDataScadenza())+"',?,?)");		
		try {
			PreparedStatement ps = connection.prepareStatement(query);	
			ps.setInt(1, s.getNumero().intValue());			
			ps.setInt(2, s.getTermineConsegna().intValue());			
			ps.setDouble(3, s.getImporto().doubleValue());
			ps.setInt(4, s.getNumeroContratto().intValue());
			ps.execute();			
			connection.close();
		} catch (SQLException e) {
			throw new QueryException("Impossibile verificare se la specifica esiste", e.getErrorCode());
		}
	}
	
	public Vector<Specifica> selectSpecificheContratto(Contratto c) throws DatabaseConnectionException, QueryException{
		try {
			connection = DriverManager.getConnection("jdbc:sqlite:"+dbFile.getAbsolutePath());
		} catch (SQLException e) {			
			throw new DatabaseConnectionException();			
		}
		String query = "select numero, data_inizio, termine_consegna, " +
				"data_scadenza, importo from specifica " +
				"where numero_contratto = ?";
		
		try {
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setInt(1, c.getNumero().intValue());
			ResultSet rs = ps.executeQuery();
			Vector<Specifica> specificheContratto = new Vector<Specifica>();
			while (rs.next()) {
				Specifica specifica = new Specifica();
				specifica.setNumero(new BigDecimal(rs.getInt(1)));				
				specifica.setDataInizio(insertDateFormat.parse(rs.getString(2)));
				specifica.setTermineConsegna(new BigDecimal(rs.getInt(3)));
				specifica.setDataScadenza(insertDateFormat.parse(rs.getString(4)));
				specifica.setImporto(new BigDecimal(rs.getDouble(5)));
				specifica.setNumeroContratto(c.getNumero());				
				specificheContratto.add(specifica);
			}
			connection.close();
			return specificheContratto;
			
		} catch (SQLException e) {
			throw new QueryException("Impossibile selezionare le specifiche del contratto", e.getErrorCode());
		} catch (ParseException e) {
			throw new QueryException("Impossibile leggere le date", -1);
		}
		
	}
	
	public Vector<Specifica> selectSpecifiche() throws DatabaseConnectionException, QueryException{
		try {
			connection = DriverManager.getConnection("jdbc:sqlite:"+dbFile.getAbsolutePath());
		} catch (SQLException e) {			
			throw new DatabaseConnectionException();			
		}
		String query = "select numero, data_inizio, termine_consegna, " +
				"data_scadenza, importo, numero_contratto from specifica";
		
		try {
			PreparedStatement ps = connection.prepareStatement(query);			
			ResultSet rs = ps.executeQuery();
			Vector<Specifica> specifiche = new Vector<Specifica>();
			while (rs.next()) {
				Specifica specifica = new Specifica();
				specifica.setNumero(new BigDecimal(rs.getInt(1)));				
				specifica.setDataInizio(insertDateFormat.parse(rs.getString(2)));
				specifica.setTermineConsegna(new BigDecimal(rs.getInt(3)));
				specifica.setDataScadenza(insertDateFormat.parse(rs.getString(2)));
				specifica.setImporto(new BigDecimal(rs.getDouble(5)));
				specifica.setNumeroContratto(new BigDecimal(rs.getInt(6)));				
				specifiche.add(specifica);
			}
			connection.close();
			return specifiche;
			
		} catch (SQLException e) {
			throw new QueryException("Impossibile selezionare le specifiche", e.getErrorCode());
		} catch (ParseException e) {
			throw new QueryException("Impossibile leggere le date", -1);
		}
		
	}
	
	public void eliminaSpecifica(Specifica specifica ) throws DatabaseConnectionException, QueryException{
		try {
			connection = DriverManager.getConnection("jdbc:sqlite:"+dbFile.getAbsolutePath());
		} catch (SQLException e) {			
			throw new DatabaseConnectionException();
			
		}
		String query = new String("delete from specifica where numero = ?");
		try{
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setInt(1, specifica.getNumero().intValue());			
			ps.execute();
			connection.close();
		} catch (SQLException e) {
			throw new QueryException("Impossibile prorogare il contratto", e.getErrorCode());
		}
	}
	
	public BigDecimal selectSommaImportiSpecifiche(Contratto c)throws DatabaseConnectionException, QueryException{
		try {
			connection = DriverManager.getConnection("jdbc:sqlite:"+dbFile.getAbsolutePath());
		} catch (SQLException e) {			
			throw new DatabaseConnectionException();
			
		}
		String query = new String("select sum(importo) from specifica where numero_contratto = ?");
		try{
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setInt(1, c.getNumero().intValue());			
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				BigDecimal somma = new BigDecimal(rs.getDouble(1));
				connection.close();
				return somma;
			}else{
				connection.close();
				return BigDecimal.ZERO;
			}
			
		} catch (SQLException e) {
			throw new QueryException("Impossibile calcolare la somma degli importi delle specifiche", e.getErrorCode());
		}		
	}
	
	public java.util.Date selectInizioPrimaSpecifica(Contratto c) throws DatabaseConnectionException, QueryException{
		try {
			connection = DriverManager.getConnection("jdbc:sqlite:"+dbFile.getAbsolutePath());
		} catch (SQLException e) {			
			throw new DatabaseConnectionException();
			
		}
		String query = new String("select data_inizio from specifica where numero_contratto = ? and data_inizio in (select min(data_inizio) from specifica where numero_contratto = ?)");
		try{
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setInt(1, c.getNumero().intValue());
			ps.setInt(2, c.getNumero().intValue());
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				java.util.Date data = insertDateFormat.parse(rs.getString(1));
				connection.close();
				return data;
			}else{
				connection.close();
				Calendar cal = new GregorianCalendar(1900, 0,1);
				return cal.getTime();
			}
			
		} catch (SQLException e) {
			throw new QueryException("Impossibile calcolare la data di inizio della prima specifica", e.getErrorCode());
		} catch (ParseException e) {
			throw new QueryException("Impossibile leggere le date", -1);
		}
	}
	
	public java.util.Date selectInizioUltimaSpecifica(Contratto c) throws DatabaseConnectionException, QueryException{
		try {
			connection = DriverManager.getConnection("jdbc:sqlite:"+dbFile.getAbsolutePath());
		} catch (SQLException e) {			
			throw new DatabaseConnectionException();
			
		}
		String query = new String("select data_inizio from specifica where numero_contratto = ? and data_inizio in (select max(data_inizio) from specifica where numero_contratto = ?)");
		try{
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setInt(1, c.getNumero().intValue());
			ps.setInt(2, c.getNumero().intValue());
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				java.util.Date data = insertDateFormat.parse(rs.getString(1));
				connection.close();
				return data;
			}else{
				connection.close();
				Calendar cal = new GregorianCalendar(1900, 0,1);
				return cal.getTime();
			}
			
		} catch (SQLException e) {
			throw new QueryException("Impossibile calcolare la data di inizio dell'ultima specifica", e.getErrorCode());
		} catch (ParseException e) {
			throw new QueryException("Impossibile leggere le date", -1);
		}
	}
	
	
	public void modificaSpecifica(Specifica vecchia, Specifica nuova ) throws DatabaseConnectionException, QueryException{
		try {
			connection = DriverManager.getConnection("jdbc:sqlite:"+dbFile.getAbsolutePath());
		} catch (SQLException e) {			
			throw new DatabaseConnectionException();
			
		}
		String query = new String("update specifica set " +
				"numero = ?, data_inizio = '"+insertDateFormat.format(nuova.getDataInizio())+"', termine_consegna = ?, " +
				"data_scadenza  = '"+insertDateFormat.format(nuova.getDataScadenza())+"', importo = ? where " +
				"numero = ?");
		try{
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setInt(1, nuova.getNumero().intValue());						
			ps.setInt(2, nuova.getTermineConsegna().intValue());
			ps.setDouble(3, nuova.getImporto().doubleValue());			
			ps.setInt(4, vecchia.getNumero().intValue());
			ps.execute();
			connection.close();
		} catch (SQLException e) {
			throw new QueryException("Impossibile modificare la specifica", e.getErrorCode());
		}
	}
	
	public void prorogaSpecifica(BigDecimal numero, BigDecimal termine_consegna, java.util.Date dataScadenza ) throws DatabaseConnectionException, QueryException{
		try {
			connection = DriverManager.getConnection("jdbc:sqlite:"+dbFile.getAbsolutePath());
		} catch (SQLException e) {			
			throw new DatabaseConnectionException();
			
		}
		String query = new String("update specifica set " +
				"termine_consegna = ?, data_scadenza  = '"+insertDateFormat.format(dataScadenza)+"' " +
				"where numero = ?");
		try{
			PreparedStatement ps = connection.prepareStatement(query);									
			ps.setInt(1, termine_consegna.intValue());			
			ps.setInt(2, numero.intValue());
			ps.execute();
			connection.close();
		} catch (SQLException e) {
			throw new QueryException("Impossibile prorogare la specifica", e.getErrorCode());
		}
	}
	
	

}
